package com.ifinc.common.db;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 * User: Administrator
 * Date: 2010-8-10
 * Time: 9:30:23
 */
public class BaseDao {
    protected Logger log = Logger.getLogger(this.getClass());

    protected JdbcTemplate jdbcTemplate;

    ///分页查询
    ///SELECT * FROM  ( SELECT A.*, ROWNUM RN FROM (
    // sql查询结果集
    // ) A  WHERE ROWNUM <= 查询结果的结束行  ) WHERE RN >= 查询结果的开始行
    public static final String cutPageStart = "";

    private static final String cutPageEnd = " limit ?,?";

    ///必须实现该方法，被spring框架调用
    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int update(String sql, Object... values) {
        return getJdbcTemplate().update(sql, values);
    }


    /**
     * 分页查询记录
     * 每条记录是map对象，可以直接获得各个field的值
     *
     * @param sql        sql
     * @param params     sql语句参数
     * @param start      开始的记录数
     * @param maxResults 查询的记录数
     * @return 查询结果和分页信息
     */
    public List queryList(String sql, int start, int maxResults, Object... params) {
        //添加分页参数
        Object[] newParam = addPageParam(params, start, maxResults);

        //创建分页的sql语句
        String cutPageSql = getCutPageStr(sql);

        ///执行分页查询，并返回list结果
        return getJdbcTemplate().queryForList(cutPageSql, newParam);
    }


    /**
     * 查询分页语句的sql
     * 在sql语句前后分别加分页操作的固定语句
     *
     * @return
     */
    private String getCutPageStr(String sql) {
        StringBuffer strBuffer = new StringBuffer(cutPageStart);
        strBuffer.append(sql).append(cutPageEnd);
        return strBuffer.toString();
    }


    ///构造分页查询的参数，
    private Object[] addPageParam(Object[] param, int start, int maxResults) {
        List paramList = new ArrayList();
        if (param != null) {
            for (int i = 0; i < param.length; i++) {
                paramList.add(param[i]);
            }
        }
        paramList.add(start); ///oracle查询基数从1开始，但程序一般都是从0开始
        paramList.add(maxResults);

        return paramList.toArray();
    }


    public int[] executeBatchSql(final String sql, final Object[][] paramsArr) {

        BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement preparedStatement, int s) throws SQLException {
                Object[] params = paramsArr[s];
                if (params != null) {
                    //设置sql语句参数
                    for (int i = 0; i < params.length; i++) {
                        log.debug("params[i] = " + params[i]);
                        if (params[i] != null) {
                            if (params[i] instanceof java.util.Date) {
                                preparedStatement.setTimestamp(i + 1, new Timestamp(((java.util.Date) params[i]).getTime()));
                            } else {
                                preparedStatement.setObject(i + 1, params[i]);
                            }
                        } else {
                            preparedStatement.setString(i + 1, "");
                        }
                    }
                }
            }

            public int getBatchSize() {
                return paramsArr.length;
            }
        };
        return this.getJdbcTemplate().batchUpdate(sql, setter);
    }

    public Pager findPager(String sql, Pager pager, Object... values) {
        if (pager.isAutoCount()) {
            int totalCount = exeCountSql(sql, values);
            pager.setTotalCount(totalCount);
        }
        List result = queryList(sql, pager.getFirst() - 1, pager.getPageSize(), values);
        pager.setResult(result);
        return pager;
    }


    public JdbcTemplate getJdbcTemplate() {
        return this.jdbcTemplate;
    }

    public int exeCountSql(String sql, Object... values) {
        String fromSql = sql;
        //select子句与order by子句会影响count查询,进行简单的排除.
        fromSql = "from " + StringUtils.substringAfter(fromSql, "from");
        String countSql = "select count(1) " + fromSql;
//        System.out.println("countSql = " + countSql);
        return getJdbcTemplate().queryForInt(countSql, values);
    }

    public List listAll(String sql, RowMapper rowMapper, Object... values) {
        return getJdbcTemplate().query(sql, rowMapper, values);
    }
}